/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package course;

/**
 */
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;

public class MyDatabaseHandler {

    private String url;
    private Connection c;
    private ErrorReporter errorReporter;
    private PreparedStatement getCourse;
    private PreparedStatement getOccation;
    private PreparedStatement addOccation;
    private PreparedStatement getOccation_has_Course;
    private PreparedStatement getProgram_has_Course;
    private PreparedStatement getProgram;
    private PreparedStatement addCourse;
    private PreparedStatement addProgram;
    private PreparedStatement addProgram_has_Course;
    private PreparedStatement setCode;

    private void initPreparedStatements() {
        try {
            // här anropar vi på vårt databas tabeller

            getCourse = c.prepareStatement("SELECT code, courseName, educationArea, courseType, hpPoints FROM course");
            getOccation = c.prepareStatement("SELECT ApplicationCode, Semester, Teacher,StartDate FROM occation");
            getOccation_has_Course = c.prepareStatement("SELECT ApplicationCode, Code FROM Occation_has_Course");
            getProgram = c.prepareStatement("SELECT AppCode, ProgramName FROM program");
            getProgram_has_Course = c.prepareStatement("SELECT AppCode, Code, EducationYear FROM Program_has_Course");

            addCourse = c.prepareStatement("INSERT INTO course (code, courseName, educationArea, courseType, hpPoints) values (?, ?, ?, ?, ?)");
            addOccation = c.prepareStatement("INSERT INTO occation ( applicationCode, semester, teacher, startDate) values (?,?,?,?)");
            addProgram = c.prepareStatement("INSERT INTO program (appCode, programName)values (?,?)");
            addProgram_has_Course = c.prepareStatement("INSERT INTO program_has_course (appCode, code, educationYear) values (?,?,?)");

        } catch (SQLException ex) {
            System.out.println("Failed to prepare statement. Syntax error?");
        }
    }

    /**
     * Creates a new database handler class
6     * @param errorReporter the object to which errors shall be reported
     */
    public MyDatabaseHandler(ErrorReporter errorReporter) {
        this.errorReporter = errorReporter;
        url = "jdbc:mysql://194.47.47.18:3306/group56?user=group56&password=fgfg56";
    }

    /**
     * Initialises the connection that are used to handle database queries in this class
     */
    public void connect() {
        try {
            //Load the driver
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (InstantiationException ex) {
            System.out.println("Failed to load the driver ");
            ex.printStackTrace();
            errorReporter.reportError("The database driver could not be loaded");
        } catch (IllegalAccessException ex) {
            System.out.println("Failed to load the driver: No access ");
            ex.printStackTrace();
            errorReporter.reportError("The database driver could not be loaded");
        } catch (ClassNotFoundException ex) {
            System.out.println("Failed to load the driver: Class not found ");
            ex.printStackTrace();
            errorReporter.reportError("The database driver could not be loaded");
        }

        try {
            c = DriverManager.getConnection(url);
            c.setAutoCommit(true);
            System.out.println("Successfully connected to database");
        } catch (SQLException ex) {
            System.out.println("Connection failed" + ex);
            ex.printStackTrace();
            errorReporter.reportError("Failed to connect to database");
        } catch (Exception ex) {
            System.out.println("Connection failed: Unknown error");
            ex.printStackTrace();
            errorReporter.reportError("Failed to connect to database");
        }
        initPreparedStatements();
    }

    public ArrayList<Course> getCourse() {
        ArrayList<Course> resultList = new ArrayList<Course>();
        try {
            ResultSet rs = getCourse.executeQuery();

            while (rs.next()) {
                Course co = new Course();


            // används när vi hämtar data i databasen till textfälten i GUI
                resultList.add(co);
                String code = rs.getString("Code");
                co.setCode(code);

// det som kommer vid Output
                System.out.println(co.getCode());


                String courseName = rs.getString("CourseName");
                co.setCourseName(courseName);
                System.out.println(co.getCourseName());


                String educationArea = rs.getString("EducationArea");
                co.setEducationArea(educationArea);
                System.out.println(co.getEducationArea());


                String courseType = rs.getString("CourseType");
                co.setCourseType(courseType);
                System.out.println(co.getCourseType());


                Double hpPoints = rs.getDouble("HpPoints");
                co.setHpPoints(hpPoints);
                System.out.println(co.getHpPoints());
                System.out.println("");
            }
        } catch (SQLException ex) {
            //Meddela användaren??
            ex.printStackTrace();
        }

        return resultList;
    }

    public ArrayList<Occation> getOccation() {
        ArrayList<Occation> resultList = new ArrayList<Occation>();
        try {
            ResultSet rs = getOccation.executeQuery();

            while (rs.next()) {
                Occation o = new Occation();


                resultList.add(o);
                String applicationCode = rs.getString("ApplicationCode");
                o.setApplicationCode(applicationCode);
                System.out.println(o.getApplicationCode());


                String semester = rs.getString("Semester");
                o.setSemester(semester);
                System.out.println(o.getSemester());


                String teacher = rs.getString("Teacher");
                o.setTeacher(teacher);
                System.out.println(o.getTeacher());


                Date startDate = rs.getDate("StartDate");
                o.setStartDate(startDate);
                System.out.println(o.getStartDate());
                System.out.println("");
            }
        } catch (SQLException ex) {
            //Meddela användaren??
            ex.printStackTrace();
        }

        return resultList;
    }

    public ArrayList<Occation_has_Course> getOccation_has_Course() {
        ArrayList<Occation_has_Course> resultList = new ArrayList<Occation_has_Course>();
        try {
            ResultSet rs = getOccation_has_Course.executeQuery();

            while (rs.next()) {
                Occation_has_Course oc = new Occation_has_Course();


                resultList.add(oc);
                String applicationCode = rs.getString("ApplicationCode");
                oc.setApplicationCode(applicationCode);
                System.out.println(oc.getApplicationCode());


                String code = rs.getString("Code");
                oc.setCode(code);
                System.out.println(oc.getCode());

            }
        } catch (SQLException ex) {
            //Meddela användaren??
            ex.printStackTrace();
        }

        return resultList;
    }

    public ArrayList<Program> getProgram() {
        ArrayList<Program> resultList = new ArrayList<Program>();
        try {
            ResultSet rs = getProgram.executeQuery();

            while (rs.next()) {
                Program p = new Program();

            // används när vi hämtar data i databasen till textfälten i GUI
                resultList.add(p);
                String appCode = rs.getString("AppCode");
                p.setAppCode(appCode);

// det som kommer vid Output
                System.out.println(p.getAppCode());


                String programName = rs.getString("ProgramName");
                p.setProgramName(programName);
                System.out.println(p.getProgramName());

            }
        } catch (SQLException ex) {
            //Meddela användaren??
            ex.printStackTrace();
        }

        return resultList;
    }

    public ArrayList<Program_has_Course> getProgram_has_Course() {
        ArrayList<Program_has_Course> resultList = new ArrayList<Program_has_Course>();
        try {
            ResultSet rs = getProgram_has_Course.executeQuery();

            while (rs.next()) {
                Program_has_Course pc = new Program_has_Course();


                resultList.add(pc);
                String appCode = rs.getString("AppCode");
                pc.setAppCode(appCode);
                System.out.println(pc.getAppCode());

                resultList.add(pc);
                String code = rs.getString("Code");
                pc.setCode(code);
                System.out.println(pc.getCode());

                int educationYear = rs.getInt("EducationYear");
                pc.setEducationYear(educationYear);
                System.out.println(pc.getEducationYear());

            }
        } catch (SQLException ex) {
            //Meddela användaren??
            ex.printStackTrace();
        }

        return resultList;
    }

    public void addCourse(String code, String courseName, String educationArea, String courseType, double hpPoints) {

        try {
            addCourse.setString(1, code);
            addCourse.setString(2, courseName);
            addCourse.setString(3, educationArea);
            addCourse.setString(4, courseType);
            addCourse.setDouble(5, hpPoints);

            addCourse.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("Error when executing insert statement");
            ex.printStackTrace();
            //We failed so we do not want to proceed.
            errorReporter.reportError("Failed to add new course");
            return;
        }
    }

    public void addOccation(String applicationCode, String semester, String teacher, Date startDate) {

        try {
            addOccation.setString(1, applicationCode);
            addOccation.setString(2, semester);
            addOccation.setString(3, teacher);
            addOccation.setDate(4, null);

            addOccation.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("Error when executing insert statement");
            ex.printStackTrace();
            //We failed so we do not want to proceed.
            errorReporter.reportError("Failed to add new occation");
            return;
        }
    }

    public void addProgram(String appCode, String programName) {

        try {
            addProgram.setString(1, appCode);
            addProgram.setString(2, programName);

            addProgram.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("Error when executing insert statement");
            ex.printStackTrace();
            //We failed so we do not want to proceed.
            errorReporter.reportError("Failed to add new Program");
            return;
        }
    }

    public void addProgram_has_course(String appCode, String code, int educationYear) {

        try {
            addProgram_has_Course.setString(1, appCode);
            addProgram_has_Course.setString(2, code);
            addProgram_has_Course.setInt(3, educationYear);

            addProgram_has_Course.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("Error when executing insert statement");
            ex.printStackTrace();
            //We failed so we do not want to proceed.
            errorReporter.reportError("Failed to add new course");
            return;
        }
    }

    public void setCode(String code) {

        try {
            setCode.setString(1, code);
            setCode.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("Error when executing insert statement");
            ex.printStackTrace();
            //We failed so we do not want to proceed.
            errorReporter.reportError("Failed to add new code");
            return;
        }
    }

    /**
     * Closes the connection
     */
    public void disConnect() {
        try {
            c.close();
            System.out.println("Successfully disconnected from database");
        } catch (SQLException ex) {
            System.out.println("Failed to disconnect from database server");
            ex.printStackTrace();

        }
    }
}
